﻿Imports System.Data.SqlClient

Public Class cCase

    Dim con As New SqlConnection(strcon)

    'For choose for caseincome form
    Public Function select_Case_All() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.CaseID,m.MainCaseName,m.CaseCategoryName,m.LawyerName,c.CaseNo,c.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from vCase c INNER JOIN vMainCase m ON c.MainCaseID=m.MainCaseID")
        Return dt
    End Function

    Public Function select_Case_Finish() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.CaseID,m.MainCaseName,m.CaseCategoryName,m.LawyerName,c.CaseNo,c.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from vCase c INNER JOIN vMainCase m ON c.MainCaseID=m.MainCaseID WHERE c.IsFinish=1")
        Return dt
    End Function

    Public Function select_Case_NotFinish() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.CaseID,m.MainCaseName,m.CaseCategoryName,m.LawyerName,c.CaseNo,c.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from vCase c INNER JOIN vMainCase m ON c.MainCaseID=m.MainCaseID WHERE c.IsFinish=0")
        Return dt
    End Function

    Public Function select_Case_All_byKeyword(ByVal Keyword As String)
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.CaseID,m.MainCaseName,m.CaseCategoryName,m.LawyerName,c.CaseNo,c.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from vCase c INNER JOIN vMainCase m ON c.MainCaseID=m.MainCaseID WHERE LOWER(m.MainCaseName + m.CaseCategoryName + m.LawyerName + c.CaseNo + c.CourtName + c.Tel + CONVERT(nvarchar,c.CaseFee) + CONVERT(nvarchar,c.SuccessfulCase) + c.UserID) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_Case_Finish_byKeyword(ByVal Keyword As String)
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.CaseID,m.MainCaseName,m.CaseCategoryName,m.LawyerName,c.CaseNo,c.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from vCase c INNER JOIN vMainCase m ON c.MainCaseID=m.MainCaseID WHERE c.IsFinish=1 AND LOWER(m.MainCaseName + m.CaseCategoryName + m.LawyerName + c.CaseNo + c.CourtName + c.Tel + CONVERT(nvarchar,c.CaseFee) + CONVERT(nvarchar,c.SuccessfulCase) + c.UserID) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_Case_NotFinish_byKeyword(ByVal Keyword As String)
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.CaseID,m.MainCaseName,m.CaseCategoryName,m.LawyerName,c.CaseNo,c.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from vCase c INNER JOIN vMainCase m ON c.MainCaseID=m.MainCaseID WHERE c.IsFinish=0 AND LOWER(m.MainCaseName + m.CaseCategoryName + m.LawyerName + c.CaseNo + c.CourtName + c.Tel + CONVERT(nvarchar,c.CaseFee) + CONVERT(nvarchar,c.SuccessfulCase) + c.UserID) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function
    'End For choose fo caseincome form


    'For View
    Public Function select_Case_byMainCaseID(ByVal MainCaseID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("select c.CaseID,c.MainCaseID,c.CaseNo,co.CourtName,c.Tel,c.CaseFee,c.SuccessfulCase,c.StartDate,c.TotalDays,c.IsFinish,c.UserID from tblCase c INNER JOIN tblCourt co ON c.CourtID=co.CourtID WHERE MainCaseID=N'" & MainCaseID & "'")
        Return dt
    End Function
    'End For View

    'For View at Entry Form
    Public Function select_Case_byID(ByVal ID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT * FROM tblCase WHERE CaseID=N'" & ID & "'")
        Return dt
    End Function
    'End For View at Entry Form

    Public Function insert_Case_ID(ByVal CaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblCase(CaseID) VALUES(@CaseID)"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_Case_ID(ByVal CaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblCase WHERE CaseID=@CaseID"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function insert_Case(ByVal MainCaseID As Long, ByVal MainCaseName As String, ByVal CaseCategoryID As Long, ByVal LawyerID As Long, ByVal UserID As String, _
                                ByVal CaseID As Long, ByVal CaseNo As String, ByVal CourtID As Long, ByVal Tel As String, ByVal CaseFee As Double, ByVal SuccessfulCase As Double, ByVal StartDate As Date) As Boolean
        Dim rv As Boolean = True
        Dim con As New SqlConnection(strcon)
        Dim T As SqlTransaction
        con.Open()
        T = con.BeginTransaction

        Dim cmd1 As New SqlCommand("UPDATE tblMainCase SET MainCaseName=@MainCaseName,CaseCategoryID=@CaseCategoryID,LawyerID=@LawyerID,UserID=@UserID WHERE MainCaseID=@MainCaseID", con, T)
        With cmd1.Parameters
            .AddWithValue("@MainCaseID", MainCaseID)
            .AddWithValue("@MainCaseName", MainCaseName)
            .AddWithValue("@CaseCategoryID", CaseCategoryID)
            .AddWithValue("@LawyerID", LawyerID)
            .AddWithValue("@UserID", UserID)
        End With
        cmd1.ExecuteNonQuery()

        Dim cmd2 As New SqlCommand("UPDATE tblCase SET MainCaseID=@MainCaseID,CaseNo=@CaseNo,CourtID=@CourtID,Tel=@Tel,CaseFee=@CaseFee,SuccessfulCase=@SuccessfulCase,StartDate=@StartDate,UserID=@UserID WHERE CaseID=@CaseID", con, T)
        With cmd2.Parameters
            .AddWithValue("@CaseID", CaseID)
            .AddWithValue("@MainCaseID", MainCaseID)
            .AddWithValue("@CaseNo", CaseNo)
            .AddWithValue("@CourtID", CourtID)
            .AddWithValue("@Tel", Tel)
            .AddWithValue("@CaseFee", CaseFee)
            .AddWithValue("@SuccessfulCase", SuccessfulCase)
            .AddWithValue("@StartDate", StartDate)
            .AddWithValue("@UserID", UserID)
        End With
        cmd2.ExecuteNonQuery()

        Try
            T.Commit()
        Catch ex As Exception
            T.Rollback()
            rv = False
        Finally
            con.Close()
        End Try
        Return rv
    End Function

    'Finish Case
    Public Function update_Case_Finish(ByVal CaseID As Long, ByVal TotalDays As Integer) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblCase SET TotalDays=@TotalDays,IsFinish=1 WHERE CaseID=@CaseID"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
            .AddWithValue("@TotalDays", TotalDays)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function
    'End Finish Case

End Class
